﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Hosting;
using System.Data.SqlClient;
using System.Web.Security;

namespace OrmAuto
{
		public class QueryUtenti : ABaseEF
		{

				public QueryUtenti()
				{

				}

				public IEnumerable<string> getRuoliUtente(string pUserName)
				{
						using (var DAL = getDAL())
						{
								var q = DAL.UsersInRoles.Where(u => u.UserName == pUserName).Select(s => s.Roles.RoleName);
								return q.ToList();
						}
				}//fM

				public IEnumerable<string> getRuoliUtente(int pUserID)
				{
						using (var DAL = getDAL())
						{
								var query = DAL.Users.Where(uu => uu.UserId == pUserID).Select(uu => uu.UserName);
								var q = DAL.UsersInRoles.Where(u => u.UserName == query.SingleOrDefault()).Select(r => r.Roles.RoleName).ToList();
								return q.ToList();
						}
				}//fM


				public IEnumerable<string> getTuttiRuoli()
				{
						using (var DAL = getDAL())
						{
								return DAL.Roles.OrderBy(r => r.RoleName).Select(r => r.RoleName).ToList();
						}
				}//fM

				/// <summary>
				/// Conta tutti gli utenti presenti
				/// </summary>
				/// <returns></returns>
				public int getUtentiCount()
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Count();
						}
				}//fM

				/// <summary>
				/// Conta gli utenti abiilitati o meno
				/// </summary>
				/// <returns></returns>
				public int getUtentiAbilitatiCount(bool pEnabled)
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Count(u => u.Enabled == pEnabled);
						}
				}//fM

				public IEnumerable<Users> getUtenti()
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.OrderBy(u => u.DateCreated).ToList();
						}
				}//fM

				public IEnumerable<Users> getUtentiAbilitati(bool pEnabled)
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Where(u => u.Enabled == pEnabled).OrderBy(u => u.DateCreated).ToList();
						}
				}//fM


				public Users getUtente(int pIDUtente)
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Where(u => u.UserId == pIDUtente).FirstOrDefault();
						}
				}//fM

				public int getUtenteID(string pUserName)
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Where(u => u.UserName == pUserName).Select(u=> u.UserId ).FirstOrDefault();
						}
				}//fM

				public Users getUtenteByUserName(string pUserName)
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Where(u => u.UserName == pUserName).FirstOrDefault();
						}
				}//fM

				public Users getUtenteByEmail(string pEmail)
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Where(u => u.Email == pEmail).FirstOrDefault();
						}
				}//fM


				public Users getUtenteByPwd(string pPassword)
				{
						using (var DAL = getDAL())
						{
								return DAL.Users.Where(u => ComputeSHA512(pPassword + u.PasswordSalt) == u.PasswordHash).FirstOrDefault();
						}
				}//fM


				public string getRandomPassword(int length, int numOfSpecialChar)
				{
						return System.Web.Security.Membership.GeneratePassword(length, numOfSpecialChar);
				}

				//con le convenzioni di altairs assembly
				public void setPasswordUtente(string username, string password)
				{
						// Generate new password hash and salt
						string passwordSalt = Membership.GeneratePassword(5, 1);
						string passwordHash = ComputeSHA512(password + passwordSalt);

						// Update password in database
						try
						{
								using (HostingEnvironment.Impersonate())
								{
										using (var DAl = getDAL())
										{
												var ut = DAl.Users.Where(u => u.UserName == username).FirstOrDefault();
												if (ut == null) throw new ArgumentException("UserName non trovato");

												ut.PasswordHash = passwordHash;
												ut.PasswordSalt = passwordSalt;
												DAl.SaveChanges();
										}
								}
						}
						catch { throw; } // Security context hack for HostingEnvironment.Impersonate
						
				}
				
				public DatiUtenti getDatiUtente(string pUserName)
				{
						using (var DAL = getDAL())
						{
								return DAL.DatiUtenti.Where(du=> du.Users.UserName == pUserName  ).FirstOrDefault();
						}
				}//fM

				public DatiUtenti getDatiUtente(int pIdUtente)
				{
						using (var DAL = getDAL())
						{
								return DAL.DatiUtenti.Include("TipoDitte").Include("TipiVenditori").Include("stati").Include("stati1") 
										.Where(du => du.Users.UserId == pIdUtente).FirstOrDefault();
						}
				}//fM

				public Users getUtenteCompleto(int? pUserID,string pUserName )
				{
						using (var DAL = getDAL())
						{
						  IQueryable<Users> query =null;
						  if(pUserID.HasValue)
										query = DAL.Users.Include("DatiUtenti").Where(u => u.UserId == pUserID.Value);

								if (! string.IsNullOrEmpty(pUserName))
										query = DAL.Users.Include("DatiUtenti").Where(u => u.UserName == pUserName);
										
								if(query == null)return null;
								return query.FirstOrDefault(); 
						}
				}//fM

				
				public  IList<STATI> getStati()
				{
						using (var DAL = getDAL())
						{
						 return DAL.STATI.ToList();						 
						}				
				}//fM

				public IList<REGIONI> getRegioni(int? pIDStato)
				{
						using (var DAL = getDAL())
						{
						  if(pIDStato== null)
										return DAL.REGIONI.OrderBy(r=> r.REG_REGIONE).ToList();
								else
										return DAL.REGIONI.Where(r => r.STATI.STATO_ID == pIDStato.Value).OrderBy(r => r.REG_REGIONE).ToList();
						}	
				}//fM

				public IList<PROVINCE> getProvince(int? pRegionID)
				{
						using (var DAL = getDAL())
						{
						  if(pRegionID == null)
										return DAL.PROVINCE.OrderBy(p=> p.PROV_PROVINCIA) .ToList();
								else
										return DAL.PROVINCE.Where(p => p.REGIONI.REG_ID == pRegionID.Value).
												OrderBy(p => p.PROV_PROVINCIA).ToList();		
						}	
				}//fM

				public IList<Comuni> getCittà()
				{
						using (var DAL = getDAL())
						{
								return DAL.Comuni.ToList();
						}	
				}//fM
								
				private string ComputeSHA512(string s)
				{
						if (string.IsNullOrEmpty(s)) throw new ArgumentNullException();
						byte[] buffer = System.Text.Encoding.UTF8.GetBytes(s);
						buffer = System.Security.Cryptography.SHA512Managed.Create().ComputeHash(buffer);
						return System.Convert.ToBase64String(buffer).Substring(0, 86); // strip padding
				}
				
				

		}//fC
}
